﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using VacationDaysCommons;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.SqlServerCe;
using VacationDaysCommons.VDExceptions;

namespace VacationDaysDatabase
{
    public class DataAccess
    {
        private SqlCeConnection connection;
        private string ConnString
        {
            
            get { return "Data Source=C:\\VacationDaysDatabase.sdf;Password='nascar';"; }
            //get { return System.Configuration.ConfigurationManager.ConnectionStrings["VDDBConnection"].ConnectionString; }
            //ConfigurationManager.ConnectionStrings["VDDBConnection"].ConnectionString; }
        }

        DataAccess()
        {
            //use this first time only 3.x -> 4, otherwise no fun!
            //SqlCeEngine en = new SqlCeEngine(ConnString);
            //if (en != null)
            //{
            //    en.Upgrade();
            //}
            connection = new SqlCeConnection(ConnString);
            if (connection != null)
            {
                connection.Open();
            }
        }

        ~DataAccess()
        {
            if (connection != null)
            {
                connection.Close();
                connection = null;
            }

        }

        public static DataAccess Instance
        {
            get
            {
                return Nested.instance;
            }
        }

        class Nested
        {
            static Nested() { }
            internal static readonly DataAccess instance = new DataAccess();
        }

        
        public List<VDEmployee> GetEmployees(int startIndex, int count)
        {
            string GetEmployeeRange = "SELECT ID, Username, Role, LastName, FirstName, Email, Passhash, PM FROM VDUsers ORDER BY RowID OFFSET @offset ROWS FETCH NEXT @rowcount ROWS ONLY;";
            List<VDEmployee> employees = new List<VDEmployee>();

            using (SqlCeCommand cmd = new SqlCeCommand(GetEmployeeRange, connection))
            {
                cmd.Parameters.AddWithValue("offset", startIndex);
                cmd.Parameters.AddWithValue("rowcount", count);
                try
                {
                    using (SqlCeResultSet resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
                    {

                        foreach (SqlCeUpdatableRecord record in resultSet)
                        {
                            employees.Add(new VDEmployee()
                            {
                                Id = record.GetGuid(record.GetOrdinal("ID")),
                                UserName = record.GetString(record.GetOrdinal("Username")),
                                Role = (EmployeeRole)record.GetInt16(record.GetOrdinal("Role")),
                                FirstName = record.GetString(record.GetOrdinal("FirstName")),
                                LastName = record.GetString(record.GetOrdinal("LastName")),
                                Email = record.GetString(record.GetOrdinal("Email")),
                                Password = record.GetString(record.GetOrdinal("Passhash")),
                                PM = record.GetGuid(record.GetOrdinal("PM"))
                            });
                        }
                    }
                }
                catch (SqlCeException e)
                {

                }

                return employees;
            }
        }

        public VDEmployee GetEmployee(string username, string password)
        {
            string SelectEmployee = "SELECT ID, Username, Role, LastName, FirstName, Email, Passhash, PM FROM VDUsers where Username=@UserName AND Passhash=@Passhash;";
        
            VDEmployee employee;
            using (SqlCeCommand cmd = new SqlCeCommand(SelectEmployee, connection))
            {
                    cmd.Parameters.AddWithValue("UserName", username);
                    cmd.Parameters.AddWithValue("Passhash", password);
                    using (SqlCeResultSet resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
                    {

                        var query = from SqlCeUpdatableRecord record in resultSet
                                    select new VDEmployee()
                                    {
                                        Id = record.GetGuid(record.GetOrdinal("ID")),
                                        UserName = record.GetString(record.GetOrdinal("Username")),
                                        Role = (EmployeeRole)record.GetInt16(record.GetOrdinal("Role")),
                                        FirstName = record.GetString(record.GetOrdinal("FirstName")),
                                        LastName = record.GetString(record.GetOrdinal("LastName")),
                                        Email = record.GetString(record.GetOrdinal("Email")),
                                        Password = record.GetString(record.GetOrdinal("Passhash")),
                                        PM = record.GetGuid(record.GetOrdinal("PM"))
                                    };
                        employee = query.FirstOrDefault<VDEmployee>();
                    }

                return employee;
            }
            

        }

        public List<VDEmployee> GetProjectManagers()
        {
            string GetAllPMs = "SELECT ID, Username FROM VDUsers where Role=1";
            List<VDEmployee> employees = new List<VDEmployee>();

            using (SqlCeCommand cmd = new SqlCeCommand(GetAllPMs, connection))
            {
                try
                {
                    using (SqlCeResultSet resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
                    {

                        foreach (SqlCeUpdatableRecord record in resultSet)
                        {
                            employees.Add(new VDEmployee()
                            {
                                Id = record.GetGuid(record.GetOrdinal("ID")),
                                UserName = record.GetString(record.GetOrdinal("Username")),
                            });
                        }
                    }
                }
                catch (SqlCeException e)
                {

                }

                return employees;
            }
        }

        public List<VDEmployee> GetHRPeople()
        {
            string GetAllHRs = "SELECT ID, Username FROM VDUsers where Role=2";
            List<VDEmployee> employees = new List<VDEmployee>();

            using (SqlCeCommand cmd = new SqlCeCommand(GetAllHRs, connection))
            {
                try
                {
                    using (SqlCeResultSet resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
                    {

                        foreach (SqlCeUpdatableRecord record in resultSet)
                        {
                            employees.Add(new VDEmployee()
                            {
                                Id = record.GetGuid(record.GetOrdinal("ID")),
                                UserName = record.GetString(record.GetOrdinal("Username")),
                            });
                        }
                    }
                }
                catch (SqlCeException e)
                {

                }

                return employees;
            }
        }

        public VDEmployee TryCreateNewUser(VDEmployee newUserData)
        {
            string CreateNewEmployee = "insert into VDUsers (ID, Username, Passhash, Role, LastName, FirstName, Email, PM) values (@id, @name, @pass, @role, @lastname, @firstname, @email, @pm)";
            using (SqlCeCommand cmd = new SqlCeCommand(CreateNewEmployee, connection))
            {
                
                cmd.Parameters.AddWithValue("id", Guid.NewGuid().ToString());
                cmd.Parameters.AddWithValue("name", newUserData.UserName);
                cmd.Parameters.AddWithValue("pass", newUserData.Password);
                cmd.Parameters.AddWithValue("role", ((int)newUserData.Role).ToString());
                cmd.Parameters.AddWithValue("lastname", newUserData.LastName);
                cmd.Parameters.AddWithValue("firstname", newUserData.FirstName);
                cmd.Parameters.AddWithValue("email", newUserData.Email);
                cmd.Parameters.AddWithValue("pm", Guid.Empty);

                try
                {
                    int affectedrows = cmd.ExecuteNonQuery();
                    
                }
                catch (SqlCeException e)
                {
                    switch (e.NativeError)
                    {
                        case 25016:// duplicate insert!
                            // we know that we cannot have duplicate usernames or e-mail addresses
                            var duplicateColumn = GetDuplicateColumn(e.Message);
                            if (duplicateColumn.ToLower().CompareTo("username") == 0)//<-- UGLY
                            {
                                //return new VDEmployee() { UserName = newUserData.UserName, Id = Guid.Empty};
                                throw new VDUsernameExistsException();
                            }
                            if (duplicateColumn.ToLower().CompareTo("email") == 0)//<-- still ugly
                            {
                                //return new VDEmployee() { Email = newUserData.Email, Id = Guid.Empty };
                                throw new VDEmailExistsException();
                            }
                            break;
                        default:
                            throw new VDUnknownErrorException();
                    }

                }
                catch (Exception)
                {
                    throw new VDUnknownErrorException();
                }
                return GetEmployee(newUserData.UserName, newUserData.Password); // return new object with the details of the fresh user
            }
        }

        public bool TryUpdateUser(VDEmployee updatedUserInfo)
        {
            string UpdateUserById = "UPDATE VDUsers SET LastName=@lastname, FirstName=@firstname, Role=@role, PM=@pmid WHERE ID=@id;";
            using (SqlCeCommand cmd = new SqlCeCommand(UpdateUserById, connection))
            {

                cmd.Parameters.AddWithValue("id", updatedUserInfo.Id.ToString());
                cmd.Parameters.AddWithValue("role", ((int)updatedUserInfo.Role).ToString());
                cmd.Parameters.AddWithValue("lastname", updatedUserInfo.LastName);
                cmd.Parameters.AddWithValue("firstname", updatedUserInfo.FirstName);
                cmd.Parameters.AddWithValue("pmid", updatedUserInfo.PM);

                try
                {
                    int affectedrows = cmd.ExecuteNonQuery();
                    if (affectedrows > 0)
                        return true;
                }
                catch (SqlCeException e)
                {
                    return false;

                }
                return false;
            }
        }

        public bool TryCreateVacationRequest(VDVacationRequest newVacation)
        {
            
            string CreateNewEmployeeVacation = "INSERT into VDVacationRequests (UserID, StartDate, Duration, State, Context) values (@userid, @startdate, @duration, @state, @context)";

            using (SqlCeCommand cmd = new SqlCeCommand(CreateNewEmployeeVacation, connection))
            {

                cmd.Parameters.AddWithValue("userid", newVacation.UserID.ToString());
                cmd.Parameters.AddWithValue("startdate", newVacation.StartDate.ToString());
                cmd.Parameters.AddWithValue("duration", newVacation.Duration.ToString());
                cmd.Parameters.AddWithValue("state", ((int)newVacation.RequestState).ToString());
                cmd.Parameters.AddWithValue("context", newVacation.Context);

                try
                {
                    int affectedrows = cmd.ExecuteNonQuery();
                    if (affectedrows > 0)
                    {
                        return true;
                    }
                    else { 
                        return false; 
                    }
                }
                catch (SqlCeException e)
                {
                    return false;
                }
            }
        }

        public bool TryCancelVacationRequest(int RequestID)
        {
            string CancelVacationRequest = "DELETE FROM VDVacationRequests WHERE ID=@id;";
            using (SqlCeCommand cmd = new SqlCeCommand(CancelVacationRequest, connection))
            {

                cmd.Parameters.AddWithValue("id", RequestID.ToString());
                try
                {
                    int affectedrows = cmd.ExecuteNonQuery();

                    if (affectedrows > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (SqlCeException e)
                {
                    return false;
                }
            }            
        }

        public List<int> TryCancelVacationRequests(List<int> requestIDs)
        {
            var updatedRequests = new List<int>();

            foreach (var requestID in requestIDs)
            {
                if (TryCancelVacationRequest(requestID))
                {
                    updatedRequests.Add(requestID);
                }
            }
            return updatedRequests;
        }

        public List<VDVacationRequest> GetEmployeeVacationRequests(Guid userId)
        {
            string GetVacationRequestsByEmployeeId = "SELECT ID, StartDate, Duration, State, Context from VDVacationRequests WHERE UserID=@userid;";
           
            List<VDVacationRequest> employeeRequests = new List<VDVacationRequest>();
            using (SqlCeCommand cmd = new SqlCeCommand(GetVacationRequestsByEmployeeId, connection))
            {
                cmd.Parameters.AddWithValue("userid", userId.ToString());
                try
                {
                    using (SqlCeResultSet resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
                    {

                        foreach (SqlCeUpdatableRecord record in resultSet)
                        {
                            employeeRequests.Add(new VDVacationRequest()
                            {
                                Id = record.GetInt32(record.GetOrdinal("ID")),
                                Context = record.GetString(record.GetOrdinal("Context")),
                                StartDate = record.GetDateTime(record.GetOrdinal("StartDate")),
                                Duration = record.GetInt32(record.GetOrdinal("Duration")),
                                RequestState = (VDRequestState)record.GetInt16(record.GetOrdinal("State"))
                            });
                        }
                    }
                }
                catch (SqlCeException e)
                {

                }

                return employeeRequests;
            }
        }

        public List<VDVacationRequest> GetPendingRequestsByPM(Guid pmId)
        {
            string GetPendingVacationsByPM = @"SELECT r.ID as RequestID, u.ID as EmployeeID, u.Username as EmployeeUsername, 
                                                          r.StartDate as RequestStartDate, r.Duration as RequestDuration, r.Context as RequestContext
                                                   FROM VDUsers AS u LEFT OUTER JOIN 
                                                        VDUsers AS pm ON u.PM = pm.ID INNER JOIN 
                                                        VDVacationRequests AS r ON r.UserID = u.ID
                                                   WHERE (pm.ID = @pmid) AND (r.State=0);";

            List<VDVacationRequest> employeeRequests = new List<VDVacationRequest>();

            using (SqlCeCommand cmd = new SqlCeCommand(GetPendingVacationsByPM, connection))
            {
                cmd.Parameters.AddWithValue("pmid", pmId.ToString());
                try
                {
                    using (SqlCeResultSet resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
                    {

                        foreach (SqlCeUpdatableRecord record in resultSet)
                        {
                            employeeRequests.Add(new VDVacationRequest()
                            {
                                Id = record.GetInt32(record.GetOrdinal("RequestID")),
                                Context = record.GetString(record.GetOrdinal("RequestContext")),
                                StartDate = record.GetDateTime(record.GetOrdinal("RequestStartDate")),
                                Duration = record.GetInt32(record.GetOrdinal("RequestDuration")),
                                UserID = record.GetGuid(record.GetOrdinal("EmployeeID")),
                                UserName = record.GetString(record.GetOrdinal("EmployeeUsername")),
                            });
                        }
                    }
                }
                catch (SqlCeException e)
                {

                }

                return employeeRequests;
            }
        }

        public List<VDVacationRequest> GetPendingRequestsByPMEmployee(Guid pmId, Guid empId)
        {
            string GetEmployeePendingVacationsByPM = @"SELECT r.ID as RequestID, u.ID as EmployeeID, u.Username as EmployeeUsername, 
                                                          r.StartDate as RequestStartDate, r.Duration as RequestDuration, r.Context as RequestContext
                                                   FROM VDUsers AS u LEFT OUTER JOIN 
                                                        VDUsers AS pm ON u.PM = pm.ID INNER JOIN 
                                                        VDVacationRequests AS r ON r.UserID = u.ID
                                                   WHERE (pm.ID = @pmid) AND (r.State=0) AND (u.id = @uid);";

            List<VDVacationRequest> employeeRequests = new List<VDVacationRequest>();

            using (SqlCeCommand cmd = new SqlCeCommand(GetEmployeePendingVacationsByPM, connection))
            {
                cmd.Parameters.AddWithValue("pmid", pmId.ToString());
                cmd.Parameters.AddWithValue("uid", empId.ToString());
                try
                {
                    using (SqlCeResultSet resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
                    {

                        foreach (SqlCeUpdatableRecord record in resultSet)
                        {
                            employeeRequests.Add(new VDVacationRequest()
                            {
                                Id = record.GetInt32(record.GetOrdinal("RequestID")),
                                Context = record.GetString(record.GetOrdinal("RequestContext")),
                                StartDate = record.GetDateTime(record.GetOrdinal("RequestStartDate")),
                                Duration = record.GetInt32(record.GetOrdinal("RequestDuration")),
                                UserID = record.GetGuid(record.GetOrdinal("EmployeeID")),
                                UserName = record.GetString(record.GetOrdinal("EmployeeUsername")),
                            });
                        }
                    }
                }
                catch (SqlCeException e)
                {

                }

                return employeeRequests;
            }
        }
        
        public List<VDEmployee> GetEmployeesByPM(Guid pmId)
        {
            string GetEmployeesByPMID = @"SELECT u.ID AS UserID, u.Username as UserName
                                              FROM VDUsers AS U INNER JOIN
                                                   VDUsers AS pm ON u.PM = pm.ID
                                              WHERE (pm.ID = @pmid);";

            List<VDEmployee> employees = new List<VDEmployee>();

            using (SqlCeCommand cmd = new SqlCeCommand(GetEmployeesByPMID, connection))
            {
                cmd.Parameters.AddWithValue("pmid", pmId.ToString());
                try
                {
                    using (SqlCeResultSet resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
                    {

                        foreach (SqlCeUpdatableRecord record in resultSet)
                        {
                            employees.Add(new VDEmployee()
                            {
                                Id = record.GetGuid(record.GetOrdinal("UserID")),
                                UserName = record.GetString(record.GetOrdinal("UserName")),
                            });
                        }
                    }
                }
                catch (SqlCeException e)
                {

                }

                return employees;
            }
        }
        
        /// <summary>
        /// Updates a list of requests to the desired state
        /// </summary>
        /// <param name="requestIDs">List with request IDs</param>
        /// <param name="newState">Desired state of request</param>
        /// <returns>List of successfully updated requests</returns>
        public List<int> TryUpdateRequests(List<VDVacationRequest> requests)
        {
            //string TryUpdatePendingRequest = @"UPDATE VDVacationRequests SET State=@newstate WHERE ID=@id;";
            string TryUpdateRequest = @"UPDATE VDVacationRequests 
                                               SET State=@newstate, 
                                                   StartDate=@startdate,
                                                   Duration=@duration,
                                                   Context=@context,
                                                   UserID=@userid
                                               WHERE ID=@id;";
            int errCount = 0;
            List<int> updatedRequests = new List<int>();

            foreach (var request in requests)
            {
                using (SqlCeCommand cmd = new SqlCeCommand(TryUpdateRequest, connection))
                 {
                    cmd.Parameters.AddWithValue("id", request.Id.ToString());
                    cmd.Parameters.AddWithValue("newstate", ((int)request.RequestState).ToString());
                    cmd.Parameters.AddWithValue("startdate", request.StartDate.ToString());
                    cmd.Parameters.AddWithValue("duration", request.Duration.ToString());
                    cmd.Parameters.AddWithValue("context", request.Context);
                    cmd.Parameters.AddWithValue("userid", request.UserID.ToString());
                    try
                    {
                        if (cmd.ExecuteNonQuery() != 0)
                            updatedRequests.Add(request.Id);
                    }
                    catch (SqlCeException e)
                    {
                        errCount++;
                    }
                }
            }
            return (updatedRequests);
        }

        public bool SendUserMessage(VDMessageItem messageItem)
        {
            string CreateUserMessage = @"INSERT INTO VDMessages (UserID, SenderID, MessageType, Timestamp, MessageText) 
                                         VALUES (@userid, @senderid, @msgtype, @timestamp, @msgtext);";
            using (SqlCeCommand cmd = new SqlCeCommand(CreateUserMessage, connection))
            {

                cmd.Parameters.AddWithValue("userid", messageItem.UserId.ToString());
                cmd.Parameters.AddWithValue("senderid", messageItem.SenderId.ToString());
                cmd.Parameters.AddWithValue("msgtype", ((int)messageItem.MessageType).ToString());
                cmd.Parameters.AddWithValue("timestamp", messageItem.Timestamp.ToString());
                cmd.Parameters.AddWithValue("msgtext", messageItem.MessageText ?? "");

                try
                {
                    int affectedrows = cmd.ExecuteNonQuery();
                    if (affectedrows > 0)
                    {
                        return true;
                    }
                    else { 
                        return false; 
                    }
                }
                catch (SqlCeException e)
                {
                    return false;
                }
            }
        }

        public List<VDMessageItem> GetUserMessages(Guid userID)
        {
            string GetMessagesByUserID = @"SELECT m.ID AS ID, m.MessageText AS MessageText, m.SenderID as SenderID, m.MessageType as MessageType, m.Timestamp as Timestamp, u.Username as Sendername 
                                           FROM VDMessages AS m INNER JOIN
                                                VDUsers AS u ON u.ID = m.SenderID
                                           WHERE UserID=@userid";

            List<VDMessageItem> userMessages = new List<VDMessageItem>();

            using (SqlCeCommand cmd = new SqlCeCommand(GetMessagesByUserID, connection))
            {
                cmd.Parameters.AddWithValue("userid", userID.ToString());
                try
                {
                    using (SqlCeResultSet resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
                    {
                        foreach (SqlCeUpdatableRecord record in resultSet)
                        {
                            userMessages.Add(new VDMessageItem()
                            {
                                ID = record.GetInt32(record.GetOrdinal("ID")),
                                SenderId = record.GetGuid(record.GetOrdinal("SenderID")),
                                MessageType = (VDMessageType) record.GetInt32(record.GetOrdinal("MessageType")),
                                MessageText = record.GetString(record.GetOrdinal("MessageText")),
                                Timestamp = record.GetDateTime(record.GetOrdinal("Timestamp")),
                                SenderName = record.GetString(record.GetOrdinal("Sendername"))
                            });
                        }
                    }
                }
                catch (SqlCeException e)
                {

                }

                return userMessages;
            }
        }

        public bool DeleteMessages(List<int> messageList)
        {
            string RemoveUserMessage = @"DELETE FROM VDMessages WHERE ID=@msgid;";

            foreach (var msgID in messageList)
            {
                using (SqlCeCommand cmd = new SqlCeCommand(RemoveUserMessage, connection))
                {
                    cmd.Parameters.AddWithValue("msgid", msgID.ToString());
                    try
                    {
                        int affectedrows = cmd.ExecuteNonQuery();
                        if (affectedrows > 0)
                        {
                        }
                        else
                        {
                        }
                    }
                    catch (SqlCeException e)
                    {
                    }
                }
            }
            return true;
        }










#region DIRTY_HACKS_DONT_LOOK
        private string QUERY_INFORMATION_GET_DUPLICATE_COLUMN = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @Tablename AND CONSTRAINT_NAME = @TableConstraint";
        
        private string GetDuplicateColumn(string error)
        {
            string internalError = error;
            //sample : [ Table name = VDUsers,Constraint name = UQ__VDUsers__0000000000000019 ]

            internalError = error.Substring(error.IndexOf("[")+1, error.IndexOf("]") - error.IndexOf("[")-1);
            Dictionary<string, string> constraintInfo =
                internalError.Split(',')
                          .Select(x => x.Split('='))// split by ,
                          .ToDictionary(y => y[0].Trim(), y => y[1].Trim()); // get key-value pairs
            
            string duplicateColumn;

            using (SqlCeCommand cmd = new SqlCeCommand(QUERY_INFORMATION_GET_DUPLICATE_COLUMN, connection))
            {
                    cmd.Parameters.AddWithValue("Tablename", constraintInfo["Table name"]);
                    cmd.Parameters.AddWithValue("TableConstraint", constraintInfo["Constraint name"]);
                    using (SqlCeResultSet resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
                    {
                        var query = from SqlCeUpdatableRecord record in resultSet
                                    select record.GetString(record.GetOrdinal("COLUMN_NAME"));
                        duplicateColumn = query.FirstOrDefault<string>();
                    }
             }

            return duplicateColumn;
        }
#endregion //#region DIRTY_HACKS_DONT_LOOK



    }

}